package com.lxy.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class StudentDao {


    public void insert_s(student student) {

        Connection con = ConnectDB.getConnection();
        String sql = "INSERT into s_manger(number,name,cardnumber,age,college,grade,curriculum)\n" +
                "VALUES(?,?,?,?,?,?,?)";
        try {
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setString(1, student.getNumber());
            ps.setString(2, student.getName());
            ps.setString(3, student.getAge());
            ps.setString(4, student.getGrade());
            ps.setString(5, student.getCardnumber());
            ps.setString(6, student.getCollege());
            ps.setString(7, student.getCurriculum());
            ps.executeUpdate();
            ps.close();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            ConnectDB.closeConnection(con);
        }

    }

    public List<student> select() {
        Connection con = ConnectDB.getConnection();
        String sql = "select * from s_manger ORDER BY number,cardnumber";
        List<student> list = new ArrayList();
        try {
            Statement statement = con.createStatement();
            ResultSet re = statement.executeQuery(sql); //查询
            while (re.next()) {
                student s = new student();
                s.setNumber(re.getString("number"));
                s.setName(re.getString("name"));
                s.setCurriculum(re.getString("curriculum"));
                s.setCollege(re.getString("college"));
                s.setCardnumber(re.getString("cardnumber"));
                s.setAge(re.getString("age"));
                s.setGrade(re.getString("grade"));
                list.add(s);
            }


        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            ConnectDB.closeConnection(con);
        }

        return list;
    }

    public void delete(String number){
        Connection con = ConnectDB.getConnection();
        String sql = "delete from s_manger where  number=?";

        try{
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setString(1,number);
            ps.executeUpdate();
            ps.close();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            ConnectDB.closeConnection(con);
        }
        }

        public List<student> sort(){
            Connection con = ConnectDB.getConnection();
            String sql = "select * from s_manger ORDER BY number,cardnumber";
            List<student> list = new ArrayList();
            try {
                Statement statement = con.createStatement();
                ResultSet re = statement.executeQuery(sql); //查询
                while (re.next()) {
                    student s = new student();
                    s.setNumber(re.getString("number"));
                    s.setName(re.getString("name"));
                    s.setCurriculum(re.getString("curriculum"));
                    s.setCollege(re.getString("college"));
                    s.setCardnumber(re.getString("cardnumber"));
                    s.setAge(re.getString("age"));
                    s.setGrade(re.getString("grade"));
                    list.add(s);
                }

            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                ConnectDB.closeConnection(con);
            }

            return list;


        }




}
